package idv.arthur.work.validation;

import idv.arthur.work.Widgets;
import idv.arthur.work.strucs.B1S1;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.gson.Gson;

public class Val extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    public Val() {
        super();
    }
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    	doPost(request,response);
    }

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		final int BSSE = 1;	// BSS, Subsys, Squad, Env  這四者的縮寫，在「部署」前確定這四個皆有設定
		final int valType = Integer.parseInt( request.getParameter("valType") );
		
		Widgets wid = new Widgets();
		request.setCharacterEncoding( wid.UIcodeSet );
		response.setContentType("application/text");
		response.setCharacterEncoding( wid.UIcodeSet );
		
		Connection conn = wid.getConn(true);
		PreparedStatement ps = null;
		ResultSet rsA = null;
		ResultSet rsB = null;
		ResultSet rsC = null;
		ResultSet rsD = null;
		String errPars = "}"; 
		String strSQL = null;
		
		B1S1 b1s1 = new B1S1();
		
		try {
			switch ( valType ) { // 確認測試項目
			case BSSE:
				int deplmainSN = Integer.parseInt( request.getParameter("deplmainSN") );
				strSQL = "SELECT DISTINCT bssid FROM mpdeplunitsqu WHERE deplmainSN=?";	//檢查 系統是否設定
				errPars = wid.toJend("deplmainSN", deplmainSN);
						
				ps = conn.prepareStatement(strSQL);
				ps.setInt( 1, deplmainSN );
				rsA = ps.executeQuery();
				
				strSQL = "SELECT COUNT(*) FROM bss WHERE bssid=?";
				ps = conn.prepareStatement(strSQL);
				while ( rsA.next() ) {
					errPars =
							wid.toJend("bssid", rsA.getString("bssid"));
					
					ps.setString( 1, rsA.getString("bssid") );
					rsB = ps.executeQuery();
					rsB.next();
					if ( rsB.getInt(1) == 0 ) {
						b1s1.setOK(false);
						b1s1.setMes("\n需定義系統："+rsA.getString("bssid")+"及其下轄子系統" );
					} else {		//有定義系統
						strSQL = "SELECT DISTINCT subsysid FROM mpdeplunitsqu WHERE bssid=? AND deplmainSN=?";	//檢查 子系統是否設定
						errPars =
								wid.toJ("bssid", rsA.getString("bssid")) +
								wid.toJend("deplmainSN", deplmainSN );
						
						ps = conn.prepareStatement(strSQL);
						ps.setString( 1, rsA.getString("bssid") );
						ps.setInt( 2, deplmainSN );
						rsC = ps.executeQuery();
						
						while ( rsC.next() ) {
							strSQL = "SELECT COUNT(*) FROM subsys WHERE bssid=? AND subsysid=?";
							errPars =
									wid.toJ("bssid", rsA.getString("bssid")) +
									wid.toJend("subsysid", rsC.getString("subsysid") );
							
							ps = conn.prepareStatement(strSQL);
							ps.setString( 1, rsA.getString("bssid") );
							ps.setString(2, rsC.getString("subsysid"));
							rsB = ps.executeQuery();
							rsB.next();
							if ( rsB.getInt(1) == 0 ) {
								b1s1.setOK(false);
								b1s1.setMes(b1s1.getMes()+"需定義子系統："+rsC.getString("subsysid")+"及其下轄資源群組");
							} else {		//有定義子系統
								strSQL = "SELECT DISTINCT squadid FROM mpdeplunitsqu WHERE bssid=? AND subsysid=? AND deplmainSN=?";	//檢查 子系統是否設定
								errPars =
										wid.toJ("bssid", rsA.getString("bssid")) +
										wid.toJ("subsysid", rsC.getString("subsysid") )+
										wid.toJend("deplmainSN", deplmainSN );
								
								ps = conn.prepareStatement(strSQL);
								ps.setString( 1, rsA.getString("bssid") );
								ps.setString( 2, rsC.getString("subsysid") );
								ps.setInt( 3, deplmainSN );
								rsD = ps.executeQuery();
								
								while ( rsD.next() ) {
									strSQL = "SELECT COUNT(*) FROM squad WHERE bssid=? AND subsysid=? AND squadid=?";
									errPars =
											wid.toJ("bssid", rsA.getString("bssid")) +
											wid.toJ("subsysid", rsC.getString("subsysid") )+
											wid.toJend("squadid", rsD.getString("squadid") );
									
									ps = conn.prepareStatement(strSQL);
									ps.setString( 1, rsA.getString("bssid") );
									ps.setString( 2, rsC.getString("subsysid") );
									ps.setString(3, rsD.getString("squadid") );
									rsB = ps.executeQuery();
									rsB.next();
									if ( rsB.getInt(1) == 0 ) {
										b1s1.setOK(false);
										b1s1.setMes(b1s1.getMes()+"\n需定義資源群組："+rsD.getString("squadid") );
									} else {		//有定義資源群組
										if ( !b1s1.getMes().contains("環境")  ) {	//因為環境只管有沒有，檢查一次就好，但目前在bss,subsys,squad的while loop裡可能會檢查多次
											strSQL = "SELECT COUNT(*) FROM env WHERE bssid=? AND subsysid=?";
											errPars =
													wid.toJ("bssid", rsA.getString("bssid")) +
													wid.toJend("subsysid", rsC.getString("subsysid") );
											
											ps = conn.prepareStatement(strSQL);
											ps.setString( 1, rsA.getString("bssid") );
											ps.setString( 2, rsC.getString("subsysid") );
											rsB = ps.executeQuery();
											rsB.next();
											if ( rsB.getInt(1) == 0 ) {
												b1s1.setOK(false);
												b1s1.setMes(b1s1.getMes()+"\n子系統"+rsC.getString("subsysid")+"，尚未定義環境!" );
											}											
										}
									}
								}
							}
						}
					}
				}
				
				break;
			default:
				break;
			}
		} catch (SQLException e) {
			b1s1.setOK(false);
			b1s1.setMes( wid.kem(conn, ps, e, wid.toEType("SQLException")+errPars, true) );
		} catch (Exception e) {
			b1s1.setOK(false);
			b1s1.setMes( wid.kem(conn, ps, e, wid.toEType("Exception")+errPars, true) );
		} finally {
			try {
				if ( rsA!= null ) { rsA.close(); }
				if ( rsB!= null ) { rsB.close(); }
				if ( rsC!= null ) { rsC.close(); }
				if ( rsD!= null ) { rsD.close(); }
				if ( ps!= null ) {ps.close();}
				if (conn!= null) {conn.close();}
			} catch (SQLException e) {
				b1s1.setOK(false);
				b1s1.setMes(wid.errProcess(e));
			}
		}
		response.getWriter().write( new Gson().toJson(b1s1) );		
	}
}